/**
 * 
 */
package br.embedded.socialapp.data;

import br.embedded.socialapp.domain.Usuario;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
/**
 * @author daniel
 *
 */
public class CadastroUsuarioDAO
{	
	private static final String insertSql = "insert into cadastro_usuario (user_login, first_name, last_name, passwd) values (?, ?, ?, ?);";
//	private static final String insertSql2 = "insert into mysocial_info (user_login) values (?);";
	private static final String insertSql2 = "insert into mysocial_info2 (user_login, social_network, id) values (?, ?, ?);";
	private static final String findQuery = "select t1.*, t3.network_name, t2.id from cadastro_usuario t1 inner join mysocial_info2 t2 on (t1.user_login = t2.user_login) inner join social_network t3 on (t2.social_network = t3.id_network) where t2.user_login = ? order by t2.social_network asc;";
	//	private static final String findQuery = "select t1.*, t2.orkut_id, t2.myspace_id from cadastro_usuario t1 inner join mysocial_info t2 on (t1.user_login = t2.user_login) where t1.user_login = ?;";
	private static final String loginSql = "Select user_login from cadastro_usuario where user_login = ? and passwd = ?;";
	private static final String loginSql2 = "Select user_login from cadastro_usuario where user_login = ?;";
//	private static final String updateOrkutId = "update mysocial_info set orkut_id = ? where user_login = ?;";
//	private static final String updateMySpaceId = "update mysocial_info set myspace_id = ? where user_login = ?;";
	private static final String idSql = "select user_login, id, social_network from mysocial_info2;";
	
	private static CadastroUsuarioDAO singleton;
	
	private CadastroUsuarioDAO()
	{
		ConfigurationReader.readAllParameters();
	}
	
	public static CadastroUsuarioDAO getInstance()
	{
		if (singleton == null)
			singleton = new CadastroUsuarioDAO();
		return singleton;
	}
	
	public synchronized void create(String login, String firstName, String lastName, String passwd)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.prepareStatement(insertSql);
			
			statement.setString(1, login);
			statement.setString(2, firstName);
			statement.setString(3, lastName);
			statement.setString(4, passwd);
			statement.execute();
/*			statement.close();
			
			statement = connection.prepareStatement(insertSql2);
			statement.setString(1, login);
			statement.execute();
			statement.close();
*/			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
	}
	
	public synchronized void updateSocialNetworkId(String login, String socialNetwork, String id)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			int network = socialNetwork.equalsIgnoreCase("orkut") ? 1 : 2;
			
//			PreparedStatement statement = connection.prepareStatement(query);
			statement = connection.prepareStatement(insertSql2);
			statement.setString(1, login);
			statement.setInt(2, network);
			statement.setString(3, id);
			statement.execute();
//			statement.setString(1, id);
//			statement.setString(2, login);
//			statement.executeUpdate();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
	}
	
	public synchronized Usuario getByLogin(String login)
	{
		Usuario usuario = null;
		
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			statement = connection.prepareStatement(findQuery);
			statement.setString(1, login);
			
			ResultSet resultSet = statement.executeQuery();
			
			while (resultSet.next())
			{
				if (usuario == null)
				{
					usuario = new Usuario();
					usuario.setLogin(login);
					usuario.setFirstName(resultSet.getString("first_name"));
					usuario.setLastName(resultSet.getString("last_name"));
				}
				
				String socialNetwork = resultSet.getString("network_name").toUpperCase();
				String idValue = resultSet.getString("id");
				
				if (socialNetwork.equals("ORKUT")) 
					usuario.getSocialNetworks().add(0, idValue);
				else
				{
					if (usuario.getSocialNetworks().size() == 0)
						usuario.getSocialNetworks().add(0, null);
					
					usuario.getSocialNetworks().add(1, idValue);
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return usuario;
	}
	
	public synchronized List<Usuario> getById(String id)
	{
		Hashtable<String, Usuario> usuarios = new Hashtable<String, Usuario>();
		
		Connection connection = null;
		Statement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			String findQuery2 = "select t1.*, t3.network_name, t2.id from cadastro_usuario t1 inner join mysocial_info2 t2 on (t1.user_login = t2.user_login) inner join social_network t3 on (t2.social_network = t3.id_network) where t2.user_login in (select user_login from mysocial_info2 where id in (" + id + ")) order by t2.social_network asc;";
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(findQuery2);
		
			while (resultSet.next())
			{
				String login = resultSet.getString("user_login");
				
				if (!usuarios.containsKey(login))
				{
					Usuario usuario = new Usuario();
					usuario.setLogin(login);
					usuario.setFirstName(resultSet.getString("first_name"));
					usuario.setLastName(resultSet.getString("last_name"));
					usuarios.put(login, usuario);
				}
				
				String socialNetwork = resultSet.getString("network_name").toUpperCase();
				String idValue = resultSet.getString("id");
				
				if (socialNetwork.equals("ORKUT")) 
					usuarios.get(login).getSocialNetworks().add(0, idValue);
				else
				{
					if (usuarios.get(login).getSocialNetworks().size() == 0)
						usuarios.get(login).getSocialNetworks().add(0, null);
					
					usuarios.get(login).getSocialNetworks().add(1, idValue);
				}
			}	
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return new ArrayList<Usuario>(usuarios.values());
	}
	
	public synchronized Hashtable<String, List<String>> getUsersId()
	{
		Hashtable<String, List<String>> hash = new Hashtable<String, List<String>>();
		Connection connection = null;
		Statement statement = null;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(idSql);
			
			while (resultSet.next())
			{
				String login = resultSet.getString("user_login");
				
				if (!hash.containsKey(login))
					hash.put(login, new ArrayList<String>());
				
				int socialNetwork = resultSet.getInt("social_network");
				
				if(socialNetwork == 1)
					hash.get(login).add(0, resultSet.getString("id"));
				else
				{
					if (hash.get(login).size() == 0)
						hash.get(login).add(0, null);
					
					hash.get(login).add(1, resultSet.getString("id"));
				}
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return hash;
	}
	
	public synchronized boolean checkLogin(String login)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		boolean result = false;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.prepareStatement(loginSql2);
			
			statement.setString(1, login);
			ResultSet resultSet = statement.executeQuery();
			
			result = resultSet.next();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return result;
	}
	
	public synchronized boolean checkLogin(String login, String passwd)
	{
		Connection connection = null;
		PreparedStatement statement = null;
		boolean result = false;
		
		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.prepareStatement(loginSql);
			
			statement.setString(1, login);
			statement.setString(2, passwd);
			ResultSet resultSet = statement.executeQuery();
			
			result = resultSet.next();
			//if (resultSet.next())
			//	result = true;
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
		return result;
	}
}
